﻿<%@ WebHandler Language="C#" Class="ContainerCommissionDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 工装工具分公司外委统计看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.10.18
 */

public class ContainerCommissionDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getWorkcenterData":
                        r = getWorkcenterData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCustomerData":
                        r = getCustomerData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetailData":
                        r = getDetailData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkcenterDetail":
                        r = getWorkcenterDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCustomerDetail":
                        r = getCustomerDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        getExportExcel(jObject);
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQueryString(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,ct.customername,cci.fromworkcentername,cci.productname,cci.productdescription,cci.containername,cci.qty,cci.mfgordername,mfg.qty mfgqty,
        ws.workflowstepname outstepname,ws2.workflowstepname instepname,
        s.specno,s.opdesc,sd.durationperunit,
        e.fullname outerson,cm.outdate,cm.outqty,
        e2.fullname inperson,cm.indate,cm.acceptanceqty,cm.lossqty,
        CASE WHEN cm.indate IS NOT NULL THEN '已返回' ELSE '未返回' END status
FROM containercommissioning cm
LEFT JOIN containercommidetail cmd ON cmd.containercommissioningid = cm.containercommissioningid
LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN customer ct ON ct.customerid = cm.outcustomerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = cmd.workflowstepid
LEFT JOIN Workflowstep ws2 ON ws2.workflowstepid = cm.inworkflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN employee e ON e.employeeid = cm.outemployeeid
LEFT JOIN employee e2 ON e2.employeeid = cm.inemployeeid
LEFT JOIN factory f ON f.factoryid = cm.factoryid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = cmd.workflowstepid
WHERE cm.isuse = 0
";
        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND cm.outdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND cm.outdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cm.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcentername = '{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["customername"] != null && jObject["customername"].ToString() != "")
        {
            sql += string.Format(" AND ct.customername = '{0}'", jObject["customername"].ToString());
        }

        if (jObject["customerid"] != null && jObject["customerid"].ToString() != "")
        {
            sql += string.Format(" AND cm.outcustomerid = '{0}'", jObject["customerid"].ToString());
        }

        if (jObject["status"] != null)
        {
            if (jObject["status"].ToString() == "已返回")
            {
                sql += " AND cm.indate IS NOT NULL ";
            }
        }
        return sql;
    }

    Result getWorkcenterData(JObject jObject)
    {
        string sql = @"
SELECT t.fromworkcentername,COUNT(1) total,SUM(CASE WHEN t.indate IS NOT NULL THEN 1 ELSE 0 END) inqty 
FROM ({0}) t 
WHERE t.fromworkcentername IS NOT NULL
GROUP BY t.fromworkcentername
";
        sql = string.Format(sql, getQueryString(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询各外委厂家
    Result getCustomerData(JObject jObject)
    {
        string sql = @"
SELECT t.customername,COUNT(1) total,SUM(CASE WHEN t.indate IS NOT NULL THEN 1 ELSE 0 END) inqty 
FROM ({0}) t 
WHERE t.customername IS NOT NULL
GROUP BY t.customername
";
        sql = string.Format(sql, getQueryString(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各工区外委明细
    Result getWorkcenterDetail(JObject jObject)
    {
        string sql = getQueryString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各外委厂家明细
    Result getCustomerDetail(JObject jObject)
    {
        string sql = getQueryString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getDetailData(JObject jObject)
    {
        string sql = getQueryString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        string sql = getQueryString(jObject); ;
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["customername"].Caption = "外委厂家";
        dtResult.Columns["fromworkcentername"].Caption = "工区";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["mfgqty"].Caption = "订单数";
        dtResult.Columns["outstepname"].Caption = "外委转出工序";
        dtResult.Columns["instepname"].Caption = "转入工序";
        dtResult.Columns["specno"].Caption = "工序号";
        dtResult.Columns["opdesc"].Caption = "工序名称";
        dtResult.Columns["outerson"].Caption = "外委人";
        dtResult.Columns["outdate"].Caption = "外委时间";
        dtResult.Columns["outqty"].Caption = "外委数量";
        dtResult.Columns["inperson"].Caption = "转入人";
        dtResult.Columns["indate"].Caption = "转入时间";
        dtResult.Columns["acceptanceqty"].Caption = "合格数";
        dtResult.Columns["lossqty"].Caption = "报废数";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["durationperunit"].Caption = "工时";
        ExportHelper.ExportExcel(dtResult, "外委统计看板");
    }
}